<?php
/**
 * Created by PhpStorm.
 * User: BlueIdeaLirui
 * Date: 2020/02/24
 * Time: 下午2:30
 */

namespace business\base\services;

use business\company\models\SupplierBasicModel;
use business\company\services\SupplierService;
use common\utils\DateTimeUtils;
use yii\db\Query;

/**
 * 供应商搜索标签服务类
 * Class SupplierTagsService
 * @package business\base\services
 */
class SupplierTagsService
{
    public function getContractedAmount($supplierId, $columns = 'supplier_id, amount')
    {
        return (new Query())
            ->from('`dotnet_operation`.`sc_supplier_tag_contracted_amount`')
            ->select($columns)
            ->where(['supplier_id' => $supplierId, 'is_deleted' => 0])
            ->andWhere(['>=', 'end_time', DateTimeUtils::now()])
            ->createCommand(\yii::$app->db)
            ->queryOne();
    }

    /**
     * 获取供应商标签库数据
     * @param $supplierId
     * @param string $columns
     * @return null
     * @throws \yii\db\Exception
     */
    public function getSupplierTagsById($supplierId, $columns = 'supplier_id')
    {
        return (new Query())
            ->from('`dotnet_operation`.`sc_supplier_tags`')
            ->select($columns)
            ->where(['supplier_id' => $supplierId])
            ->createCommand(\yii::$app->db)
            ->queryOne();
    }

    public function getYoucaiHundredSupplierData($supplierId, $columns = '*')
    {
        return (new Query())
            ->from('`b2b_data_center`.`youcai_hundred_supplier_data`')
            ->select($columns)
            ->where(['is_audit' => 1, 'is_deleted' => 0, 'supplier_id' => $supplierId])
            ->createCommand(\yii::$app->db)
            ->queryOne();
    }

    /**
     * 通过企业名称供应商标签库数据
     * @param $supplierName
     * @return null
     * @throws \yii\db\Exception
     */
    public function getSupplierTagsBySupplerName($supplierName, $columns = 'uid, supplier_id')
    {
        return (new Query())
            ->from('`dotnet_operation`.`sc_supplier_tags`')
            ->select($columns)
            ->where(['supplier_name' => $supplierName])
            ->createCommand(\yii::$app->db)
            ->queryOne();
    }

    /**
     * 查找该供应商的项目案例
     * @param $companyId
     * @return array
     */
    private function findProjectByCompanyId($companyId)
    {
        $sql = <<<sql
            SELECT
                ta.project_name as projectName,
                ta.uid as id,
                ta.project_remark as projectRemark,
                ta.company_id as companyId,
                ta.project_developer_name as projectCompany,
                ta.category_code as categoryCode,
                ta.project_province_code as provinceCode,
                ta.project_city_code as cityCode
            FROM
                b2b_company.uuc_supplier_project ta
            WHERE
                ta.status = 2 AND ta.is_published=1 AND ta.is_del = 0 AND
                ta.company_id =:companyId 
            ORDER BY
                ta.project_name DESC
sql;
        $sqlParams[':companyId'] = $companyId;

        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();
    }

    /**
     * 获取供应商成功案例
     * @param $supplier_id
     * @return array
     */
    public function getSupplierProjects($supplier_id)
    {
        $projectInfo = [];
        $projectList = $this->findProjectByCompanyId($supplier_id);
        $projectNameArray = $projectLocationCodeArray =
        $projectLocationNameArray = $projectDeveloperNameArray = $projectCategoryCodeArray = $projectCategoryNameArray = [];
        $categoryService = new BasicCategoryService();
        if (!empty($projectList)) {
            foreach ($projectList as $item) {
                $item['categoryCode'] = !empty($item['categoryCode']) ? explode(',', $item['categoryCode']) : [];
                !empty($item['projectName']) && array_push($projectNameArray, $item['projectName']);
                !empty($item['projectCompany']) && array_push($projectDeveloperNameArray, $item['projectCompany']);
                !empty($item['categoryCode']) && $projectCategoryCodeArray = array_merge($projectCategoryCodeArray, $item['categoryCode']);
                $categoryCode = isset($item['categoryCode'][0]) ? $item['categoryCode'][0] : null;
                $categoryName = $categoryService->getCategoryNameByCode($categoryCode);
                !empty($categoryName) && array_push($projectCategoryNameArray, $categoryName);
            }
        }

        $projectInfo['projectName'] = $projectNameArray;
        $projectInfo['projectDeveloperName'] = $projectDeveloperNameArray;
        $projectInfo['projectCategoryName'] = $projectCategoryNameArray;

        //去重
        foreach ($projectInfo as &$item) {
            //array_flip(): only flip STRING and INTEGER values
            if (!empty($item) && is_array($item)) {
                $item = array_flip($item);
                $item = array_flip($item);
                $item = array_values($item);
            }
        }

        return $projectInfo;
    }

    /**
     * 同步供应商百强合作标签数据
     * @param $supplierId
     */
    public function rsyncHundredCooperation($supplierId)
    {
        //获取项目案例信息
        $projectInfo = $this->getSupplierProjects($supplierId);
        var_dump($projectInfo);
    }

    /**
     * 同步供应商标签库
     * @param $supplierIds
     */
    public function rsyncSupplierTags(&$supplierIds)
    {
        $everyTimeNumber = 100;    //每次最大更新次数
        $supplierCount = count($supplierIds);
        $loopCount = intval($supplierCount / $everyTimeNumber) + 1;
        echo '总数: ' . $supplierCount . PHP_EOL;
        echo '正在进行: ';
        for ($i = 0; $i < $loopCount; $i++) {
            echo $i .PHP_EOL;
            for ($j = $i * $everyTimeNumber; $j < ($i + 1) * $everyTimeNumber && $j < $supplierCount; $j++) {
                $supplierId = $supplierIds[$j];
                try {
                    $supplierTagsRow = $this->getSupplierTagsById($supplierId);
                    if (empty($supplierTagsRow)) {
                        $supplier = SupplierBasicModel::findOne(['supplier_id' => $supplierId, 'status' => 2, 'state' => 2]);
                        if (!empty($supplier)) {
                            $row = [
                                'supplier_id' => $supplier->supplierId,
                                'supplier_name' => $supplier->companyName,
                                'created_by' => 'sys'
                            ];
                            $rs = \yii::$app->db->createCommand()->insert('`dotnet_operation`.`sc_supplier_tags`', $row)->execute();
                            if (!$rs) {
                                \Yii::error('rsync supplier failed! supplier_id:' . $supplierId);
                            }
                        }
                    } else {
                        \yii::$app->db->createCommand()->update('`dotnet_operation`.`sc_supplier_tags`',
                            ['updated_time' => DateTimeUtils::now(), 'updated_by' => 'sys'],
                            ['supplier_id' => $supplierId])->execute();
                    }
                    echo 'success supplier_id is ' . $supplierId . PHP_EOL;
                } catch (\Exception $e) {
                    echo 'rsync failed! supplier_id:' . $supplierId . PHP_EOL;
                }
            }
        }
        echo '处理结束' . PHP_EOL;
    }

    /**
     * 同步行业Top级数据
     * @throws \yii\base\InvalidConfigException
     */
    public function rsyncIndustryTop()
    {
        $sql = <<<sql
            SELECT
	a.supplier_id,
	b.`name`,
	a.business_area 
FROM
	`mycommunity_contractor_bigdata`.`yl_s_supplier_top_business_area` a
	INNER JOIN `mycommunity_contractor_bigdata`.yl_s_supplier b ON a.supplier_id = b.id
sql;
        $connection = \Yii::$app->get('dbSupplierBigdata');
        $result = $connection->createCommand($sql)->queryAll();
        echo '正在进行: ';
        $supplierInfo = [];
        if (!empty($result)) {
            foreach ($result as $i => $item) {
                echo $i . ' ';
                $supplerName = $item['name'];
                $categoryName = $item['business_area'];
                $suppliser = $this->getSupplierTagsBySupplerName($supplerName);

                if (!empty($suppliser)) {
                    $uid = $suppliser['uid'];
                    $suppliserId = $suppliser['supplier_id'];

                    $row = [
                        'supplier_tags_uid' => $uid,
                        'supplier_id' => $suppliserId,
                        'category_name' => $categoryName,
                        'created_by' => 'sys'
                    ];
                    $supplierInfo[$suppliserId][] = $row;
                }
            }
        }
        if (!empty($supplierInfo)) {
            $stepCols = ['supplier_tags_uid', 'supplier_id', 'category_name', 'created_by'];
            $stepRows = [];
            foreach ($supplierInfo as $supplierId => $value) {
                $supplierTags = $this->getSupplierTagsById($suppliserId, 'tag_type');
                if (!empty($supplierTags)) {
                    $tagType = array_filter(explode(',', $supplierTags['tag_type']));
                    $tagType[] = '1';
                    $tagType = array_unique($tagType);
                    \yii::$app->db->createCommand()->update('`dotnet_operation`.`sc_supplier_tags`',
                        ['tag_type' => implode(',', $tagType),'updated_time' => DateTimeUtils::now(), 'updated_by' => 'sys'],
                        ['supplier_id' => $supplierId])->execute();
                    foreach ($value as $val) {
                        $stepRows[] = $val;
                    }
                }
            }
            \yii::$app->db->createCommand()->batchInsert(
                '`dotnet_operation`.`sc_supplier_tag_industry_top`', $stepCols, $stepRows)->execute();
        }
        echo '处理结束' . PHP_EOL;
    }

    /**
     * 获取行业Top级标签项
     * @param $supplierId
     */
    public function getIndustryTopBySupplierId($supplierId)
    {
        $sql = <<<sql
            SELECT `category_name` FROM `dotnet_operation`.`sc_supplier_tag_industry_top`
sql;
        $condition = ' WHERE supplier_id = "' . $supplierId . '" AND is_deleted = 0';
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        return $result;
    }

    /**
     * 同步榜上有名数据
     * @throws \yii\base\InvalidConfigException
     */
    public function rsyncFamousList()
    {
        $sql = <<<sql
            SELECT company_id, company_name, category_name, `year` FROM b2b_op_activity.vote_supplier_rank
sql;
        $condition = ' WHERE company_type = 1 AND register_type = 1';
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        echo '正在进行: ';
        $supplierInfo = [];
        if (!empty($result)) {
            foreach ($result as $i => $item) {
                echo $i . ' ';
                $supplerId = $item['company_id'];
                $categoryName = $item['category_name'];
                $listTime = $item['year'];
                $suppliser = $this->getSupplierTagsById($supplerId, 'uid, supplier_id');

                if (!empty($suppliser)) {
                    $uid = $suppliser['uid'];
                    $suppliserId = $suppliser['supplier_id'];

                    $row = [
                        'supplier_tags_uid' => $uid,
                        'supplier_id' => $suppliserId,
                        'category_name' => $categoryName,
                        'list_time' => $listTime,
                        'created_by' => 'sys'
                    ];
                    $supplierInfo[$suppliserId][] = $row;
                }
            }
        }
        if (!empty($supplierInfo)) {
            $stepCols = ['supplier_tags_uid', 'supplier_id', 'category_name', 'list_time', 'created_by'];
            $stepRows = [];
            foreach ($supplierInfo as $supplierId => $value) {
                $supplierTags = $this->getSupplierTagsById($suppliserId, 'tag_type');
                if (!empty($supplierTags)) {
                    $tagType = array_filter(explode(',', $supplierTags['tag_type']));
                    $tagType[] = '2';
                    $tagType = array_unique($tagType);
                    \yii::$app->db->createCommand()->update('`dotnet_operation`.`sc_supplier_tags`',
                        ['tag_type' => implode(',', $tagType),'updated_time' => DateTimeUtils::now(), 'updated_by' => 'sys'],
                        ['supplier_id' => $supplierId])->execute();
                    foreach ($value as $val) {
                        $stepRows[] = $val;
                    }
                }
            }
            \yii::$app->db->createCommand()->batchInsert(
                '`dotnet_operation`.`sc_supplier_tag_famous_list`', $stepCols, $stepRows)->execute();
        }
        echo '处理结束' . PHP_EOL;
    }

    /**
     * 获取榜上有名标签项
     * @param $supplierId
     */
    public function getFamousListBySupplierId($supplierId)
    {
        $sql = <<<sql
            SELECT `category_name` FROM `dotnet_operation`.`sc_supplier_tag_famous_list`
sql;
        $condition = ' WHERE supplier_id = "' . $supplierId . '" AND is_deleted = 0';
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        return $result;
    }

    /**
     * 从优采百强战略供应商表中获取变更的id
     * @param $offset
     * @return mixed
     * @throws \yii\base\InvalidConfigException
     */
    public function getHundredSupplierIdsList($offset)
    {
        $sql = <<<sql
            SELECT uid FROM `b2b_data_center`.`youcai_hundred_supplier_data` 
            WHERE uid > :offset
            order by uid ASC
sql;
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql)
            ->bindValue(':offset', $offset)
            ->queryColumn();
        return $result;
    }

    /**
     * 同步战略合作信息数据
     * @throws \yii\base\InvalidConfigException
     */
    public function rsyncStrategyInfo($ids)
    {
        $sql = <<<sql
            SELECT developer_name, short_name, supplier_name, category_name, start_year, end_year FROM `b2b_data_center`.`youcai_hundred_supplier_data`
sql;
        $condition = ' WHERE uid in (' . implode(',', $ids) . ')';
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        echo '正在进行: ';
        if (!empty($result)) {
            foreach ($result as $i => $item) {
                echo $i . ' ';
                $supplerName = $item['supplier_name'];
                $categoryName = $item['category_name'];

                $suppliser = $this->getSupplierTagsBySupplerName($supplerName);

                if (!empty($suppliser)) {
                    $uid = $suppliser['uid'];
                    $suppliserId = $suppliser['supplier_id'];

                    $startYear = $item['start_year'] . '-01-01';
                    $endYear = $item['end_year'] . '-12-31';

                    $row = [
                        'supplier_tags_uid' => $uid,
                        'supplier_id' => $suppliserId,
                        'developer_name' => $item['developer_name'],
                        'grade' => $endYear > date('Y-m-d') ? 1 : 0,
                        'short_name' => $item['short_name'],
                        'category_name' => $categoryName,
                        'start_year' => $startYear,
                        'end_year' => $endYear
                    ];
                    $supplierInfo[$suppliserId][] = $row;
                }
            }
            if (!empty($supplierInfo)) {
                $stepCols = ['supplier_tags_uid', 'supplier_id', 'developer_name', 'grade', 'short_name', 'category_name', 'start_year', 'end_year'];
                $stepRows = [];
                foreach ($supplierInfo as $supplierId => $value) {
                    $supplierTags = $this->getSupplierTagsById($suppliserId, 'tag_type');
                    if (!empty($supplierTags)) {
                        $tagType = array_filter(explode(',', $supplierTags['tag_type']));
                        $tagType[] = '3';
                        $tagType = array_unique($tagType);
                        \yii::$app->db->createCommand()->update('`dotnet_operation`.`sc_supplier_tags`',
                            ['tag_type' => implode(',', $tagType),'updated_time' => DateTimeUtils::now(), 'updated_by' => 'sys'],
                            ['supplier_id' => $supplierId])->execute();
                        foreach ($value as $val) {
                            $stepRows[] = $val;
                        }
                        \yii::$app->db->createCommand()->batchInsert(
                            '`dotnet_operation`.`sc_supplier_tag_hundred_strategy`', $stepCols, $stepRows)->execute();
                        unset($stepRows);
                    }
                }
            }
        }
        echo '处理结束' . PHP_EOL;
    }

    /**
     * 获取百强战略合作标签项
     * @param $supplierId
     */
    public function getHundredStrategyBySupplierId($supplierId)
    {
        $sql = <<<sql
            SELECT `developer_name` FROM `dotnet_operation`.`sc_supplier_tag_hundred_strategy`
sql;
        $condition = ' WHERE supplier_id = "' . $supplierId . '" AND is_deleted = 0 AND grade = 1';
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        return $result;
    }

    /**
     * 获取百强合作标签项
     * @param $supplierId
     */
    public function getHundredCooperationBySupplierId($supplierId)
    {
        $sql = <<<sql
            SELECT `developer_name` FROM `dotnet_operation`.`sc_supplier_tag_hundred_cooperation`
sql;
        $condition = ' WHERE supplier_id = "' . $supplierId . '" AND is_deleted = 0 AND grade = 1';
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)->queryAll();
        return $result;
    }
}
